package tsli.acm.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import tsli.acm.database.Database;
import tsli.acm.model.TempAl;

public class TempAlFreeLookDAO {
	
	
	private Database db;

	public TempAlFreeLookDAO(Database db) {
		this.db = db;
	}

	public boolean insertCurrentMonthFreelook(String yyyymm,String currentYm) {
		boolean act = false;
		
			
			List<Map<String, Object>> listOfFreelook = getCurrentFreelook(yyyymm);
			for (Map<String, Object> row:listOfFreelook) {
				try {
					String sql = "insert into tsli_temp_al_freelook ( TEMP_ID,PO_CLOS_YM , PE_NO_COL , PE_NO_SM  , PO_NO , RIDER_CD, FREELOOK_YM )"
							+ "VALUES ("
							+ " '"+row.get("TEMP_ID")+"' ,"
							+ " '"+row.get("PO_CLOS_YM")+"' ,"
							+ " '"+row.get("PE_NO_COL")+"' ,"
							+ " '"+row.get("PE_NO_SM")+"' ,"
							+ " '"+row.get("PO_NO")+"' ,"
							+ " '"+row.get("RIDER_CD")+"' ,"
							+ " '"+currentYm+"' ) ";
					
//					System.out.println(sql);
					
					this.db.insert(sql);
					act = true;
				} catch (Exception ex) {
//					ex.printStackTrace();
					System.out.println("ERROR : "+ex);
					act = false;
				}
			}
			System.out.println("Info : insertCurrentMonthFreelook");  
		return act;
	}
	
	 public List<Map<String, Object>> getCurrentFreelook(String yyyymm){
		 
		 String sql = ""+
				 	"	  select ( NVL((SELECT MAX(TEMP_ID) FROM TSLI_TEMP_AL_FREELOOK),0) + ROWNUM ) AS TEMP_ID,a.*      \n"+
					"	FROM (                                                                                            \n"+
					"	select p.CLOS_YM as PO_CLOS_YM,p.PE_NO_COL,p.PE_NO_SM,n.PO_NO,n.RIDER_CD	 		\n"+
					"	FROM TSLI_GA_COMMISSION_NEGATIVE n,TSLI_GA_COMMISSION_POSITIVE p                        \n"+
					"	where n.PO_NO = p.PO_NO                                                                 \n"+
					"	and p.CLOS_YM <= ?		                                                                \n"+
					"	and p.dty_cd in ('S10240206','S10240203','S10240205')                                   \n"+
					"	and p.CLOS_YM = (                                                                       \n"+
					"	select min(CLOS_YM)                                                                     \n"+
					"	from TSLI_GA_COMMISSION_POSITIVE a                                                      \n"+
					"	where a.PO_NO = p.PO_NO                                                                 \n"+
					"	)                                                                                       \n"+
					"	--Not in freelook already count															\n"+
					"	AND (p.CLOS_YM,p.PE_NO_COL,p.PE_NO_SM,p.PO_NO,p.RIDER_CD) NOT IN		             	\n"+
					"	(                                                                                    	\n"+
					"	select l.PO_CLOS_YM as CLOS_YM,l.PE_NO_COL,l.PE_NO_SM,l.PO_NO,l.RIDER_CD             	\n"+
					"	from tsli_temp_al_freelook l                                                         	\n"+			
					"		)																					\n"+
					"	group by p.CLOS_YM , p.PE_NO_COL , p.PE_NO_SM  ,n.PO_NO  ,n.RIDER_CD, p.dty_cd          \n"+
					"	) a                                                                                     \n";
		 
		
//		System.out.println("getCurrentFreelook \n"+sql);
		 List<Map<String, Object>> result = db.queryList(sql,yyyymm);
//	     System.out.println("Info : getCurrentFreelook size : "+result.size());  
		 
	        if (result != null && result.size() > 0) {
	            return result;
	        } else {
	            return result;
	        }
	}
	 
}


